<!DOCTYPE html>
<html lang="zh-cn"><head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    
    <link href="/css/bootstrap.min.css" rel="stylesheet">
    <link href="/css/main.css" rel="stylesheet">

    <link rel="shortcut icon" type="image/x-icon" href="/img/favicon.ico" />

    <title>mariadb和mysql命令 | wiseAI的小站</title>
</head><body><div class="container-fluid">
    <nav class="navbar fixed-top navbar-expand-sm navbar-dark bg-dark">
        <div class="container-fluid">
            <a class="navbar-brand" href="/">
                <img src="/img/favicon.ico" alt="" width="30" height="24" class="d-inline-block align-text-top">
                wiseAI的小站
            </a>
            <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
                <span class="navbar-toggler-icon"></span>
            </button>
            <div class="collapse navbar-collapse" id="navbarSupportedContent">
                <ul class="navbar-nav me-auto mb-2 mb-lg-0">
                    
                    <li class="nav-item">
                        <a  class="nav-link"   href="/articles/">文章</a>
                    </li>
                    
                    <li class="nav-item">
                        <a  class="nav-link"   href="/categories/">分类</a>
                    </li>
                    
                    <li class="nav-item">
                        <a  class="nav-link"   href="/tags/">关键字</a>
                    </li>
                    
                    <li class="nav-item">
                        <a  class="nav-link"   href="/post/golang/">golang编程</a>
                    </li>
                    
                    <li class="nav-item">
                        <a  class="nav-link"   href="/about/">关于</a>
                    </li>
                    
                </ul>
                <form class="d-flex">
                    <input id="search-query" class="form-control me-2" type="search" placeholder="Search for anything..." aria-label="Search">
                </form>
            </div>
        </div>
    </nav>
</div>




<div id="content">
<div class="container article">
	<h1>mariadb和mysql命令</h1>
	<div class="post-meta">
		<div>
			
			
			By wiseai | 2022-06-01 | 2 minutes
		</div>
		<div class="tags">
			
			<a class="btn btn-light links btn-sm" href="/tags/mysql/">mysql</a>
			
			<a class="btn btn-light links btn-sm" href="/tags/mariadb/">mariadb</a>
			
		</div>
	</div>
	<div>
		<div class="article-post">
			<p>错误：ERROR 1045 (28000): Access denied for user &lsquo;root&rsquo;@&rsquo;localhost&rsquo; (using password: NO)</p>
<p>1.首先停掉数据库服务:</p>
<p><code># systemctl stop mariadb.service</code></p>
<p>2.使用mysqld_safe来启动mysqld服务器:</p>
<p><code># mysqld_safe --user=mysql --skip-grant-tables --skip-networking</code></p>
<p>3.无密码登录:</p>
<p><code># mysql -u root mysql</code></p>
<hr>
<p>命令行登录：</p>
<p><code># mysql -h 127.0.0.1 -u root -p -P 3306</code></p>
<p>新建用户：</p>
<p><code># create user 'newuser'@'localhost' identified by '123456';</code></p>
<p>或</p>
<p><code># insert into mysql.user(user,host,password) values('newuser','localhost',password('123456'));</code></p>
<p>删除用户：</p>
<p><code># DROP USER 'newuser'@'localhost';</code></p>
<p>授权:</p>
<p>注：you password获取方法：</p>
<p><code># select password('你的明文密码');</code></p>
<p>所有权限</p>
<p><code># grant all privileges on *.* to 'newuser'@'localhost' identified by password 'you password';</code></p>
<p>部分权限</p>
<p><code># grant insert,update,delete,select on *.* to 'newuser'@'localhost'</code></p>
<p>给予test数据库所有权限：</p>
<p><code># grant all on test.* to 'newuser'@'localhost'</code></p>
<p>**注意：**需要执行 <strong>FLUSH PRIVILEGES</strong> 语句重新载入授权表。</p>
<p>查看队列：</p>
<p><code># show processlist;</code></p>
<p>或者</p>
<p><code># show full processlist;</code></p>
<p>创建数据库：</p>
<p><code># CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci</code></p>
<p>选择数据库：</p>
<p><code># USE test;</code></p>
<p>修改数据库：</p>
<p><code># ALTER DATABASE test DEFAULT CHARACTER SET gb2312 DEFAULT COLLATE gb2312_chinese_ci;</code></p>
<p>删除数据库：</p>
<p><code># DROP DATABASE IF EXISTS test;</code></p>
<p>查看数据库：</p>
<p><code># SHOW DATABASES;</code></p>
<p>创建表：</p>
<pre><code>SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `blog_users`;（如果存在，删除表再添加）
CREATE TABLE `blog_users` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_login` varchar(60) NOT NULL DEFAULT '',
`user_pass` varchar(255) NOT NULL DEFAULT '',
`user_nicename` varchar(50) NOT NULL DEFAULT '',
`user_email` varchar(100) NOT NULL DEFAULT '',
`user_url` varchar(100) NOT NULL DEFAULT '',
`user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`user_activation_key` varchar(255) NOT NULL DEFAULT '',
`user_status` int(11) NOT NULL DEFAULT '0',
`display_name` varchar(250) NOT NULL DEFAULT '',
PRIMARY KEY (`ID`),
KEY `user_login_key` (`user_login`),
KEY `user_nicename` (`user_nicename`),
KEY `user_email` (`user_email`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
</code></pre>
<p>查看表：</p>
<p><code># SHOW TABLES;</code></p>
<p>查看表结构：</p>
<p><code># DESC test；</code></p>
<p>查看表如何创建：</p>
<p><code># show create table user \G;</code></p>
<p>删除表：</p>
<p><code># DROP TABLE test;</code></p>
<p>删除表中的所有数据并自增长数据从1开始：</p>
<p><code># TRUNCATE TABLE 表名;</code></p>
<p>插入数据：</p>
<p><code>insert into 'blog_users' ('ID', 'user_login', 'user_pass') values ('1','admin','$P$BnOWIsBwuIA7Hf3gZh9fVuY6sWkKmt1'); </code></p>
<p>或：</p>
<p><code># INSERT INTO test set title = '这个是title';</code></p>
<p>删除数据：</p>
<p><code># DELETE FROM test where title = '这个是title';</code></p>
<p>修改数据或更新数据：</p>
<p><code># UPDATE test set title = '这个是title';</code></p>
<p>查询：</p>
<p><code># SELECT id, title FROM test where title = '这个是title';</code></p>
<p>备份数据库：</p>
<p><code># mysqldump -uroot -p123456 数据库 &gt; /tmp/数据库.sql</code></p>
<p>恢复数据库：</p>
<p><code># mysql -uroot -p123456 数据库 &lt; /tmp/数据库.sql</code></p>
<p>备份表：</p>
<p><code># mysqldump -uroot -p 数据库 表 &gt; /tmp/表.sql</code></p>
<p>恢复表：</p>
<p><code># mysql -uroot -p 数据库 &lt; /tmp/表.sql</code></p>
<p>备份所有库：</p>
<p><code># mysqldump -uroot -p -A &gt; /tmp/123.sql</code></p>
<p>只备份表结构：</p>
<p><code># mysqldump -uroot -p -d 数据库 &gt; /tmp/数据库.sql</code></p>

		</div>
	</div>
</div>


<div class="container">
    
    <div class="row">
        
        <div class="col-5">
            <a class="page-link link-dark text-end dh" href="/post/linux%E5%B1%8F%E5%BD%95%E8%BD%AF%E4%BB%B6/">
                <h5>前一篇</h5><br>
                Linux屏录软件
            </a>            
        </div>
        
        <div class="col-2">
        </div>
        
        <div class="col-5">
            <a class="page-link link-dark text-start dh" href="/post/linux%E4%B8%AD%E7%BC%BA%E5%B0%91%E8%BF%90%E8%A1%8C%E5%BA%93%E7%9A%84%E8%A7%A3%E5%86%B3%E5%8A%9E%E6%B3%95/">
                <h5>后一篇</h5><br>
                Linux中缺少运行库的解决办法
            </a>            
        </div>
        
    </div>
    
</div>

        </div><br><br>
<footer class="container">
    <h2>友情链接</h2>
    <hr>
    <nav class="nav nav-pills flex-column flex-sm-row">
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://wiseai.gitee.io/pages/gnzg/index.html" target="_blank">孤鸟之歌</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://wiseai.gitee.io/pages/mm/index.html" target="_blank">生成随机字符</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://wiseai.gitee.io/pages/md/index.html" target="_blank">MarkDown</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://gitee.com/" target="_blank">Gitee</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://github.com/" target="_blank">Github</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://www.aliyun.com/" target="_blank">阿里云</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://cloud.tencent.com/" target="_blank">腾讯云</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://www.oschina.net/" target="_blank">OSCHINA开源中国</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://gitee.com/wiseai/the-way-to-go_ZH_CN/blob/master/eBook/directory.md" target="_blank">the way to go</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://topgoer.com/" target="_blank">golang文档</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://goframe.org/display/gf" target="_blank">GoFrame</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://www.aliyundrive.com/" target="_blank">阿里云盘</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://cn.vuejs.org/" target="_blank">vue3文档</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://element-plus.gitee.io/zh-CN/" target="_blank">element-plus文档</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://www.runoob.com/vue3/vue3-tutorial.html" target="_blank">vue3菜鸟教程</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://v5.bootcss.com/" target="_blank">bootstrap v5 中文文档</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://www.bootstrap.cn/" target="_blank">bootstrap文档</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://caddy2.dengxiaolong.com/docs/" target="_blank">caddy中文教程</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://www.58pic.com/" target="_blank">千图</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://ifonts.com/" target="_blank">iFonts</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://marketing.qiniu.com/" target="_blank">七牛云</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://www.ixigua.com/" target="_blank">西瓜视频</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://wiseai.gitee.io/pages/yugang/index.html" target="_blank">鱼缸计算</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://justcc.mengkang.net/#/" target="_blank">C语言JustCC</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://wiseai.gitee.io/pages/pptist/" target="_blank">PPTist</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="/index.xml" target="_blank">RSS</a>
        
        <a class="flex-sm-fill text-sm-center nav-link links link-dark" href="https://www.kancloud.cn/idcpj/python/418553" target="_blank">参考资料</a>
        
    </nav>

    <div class="copyright text-center">
      <span class="power-by">
        Powered by <a class="links" href="https://gohugo.io" target="_blank">Hugo</a>
    </span>
    <span>|</span>
    <span>
        Theme - <a class="links" href="https://github.com/wiseai-go/blog-hugo" target="_blank">WiseAI</a>
    </span>
    <br>
    <span class="copyright-year">
        &copy;
        
        2017 -
        2023<span>
            陇ICP备15000157号
            
        </span></span>

</div>
</footer>
<script src="/js/bootstrap.bundle.min.js"></script>


</body>
</html>
